<?php

/**
 * 本类用于在特采申请当中作判断
 * Auther QiuXiangCheng
 * Date 2016/08/19
 */
class Backup_Model_Demand extends Model_Base {

	/**
	 * 仓库类型：对应数据库
	 * @var Array
	 */
	public static $stocking_type = [
		1 => '正常备货',
		2 => '活动备货（第三方平台）',
		3 => '活动备货（自营平台）',
		4 => '活动备货（其他）',
	];

	public static $story_examine = [
		1 => '小组长审核',
		2 => '大组长审核',
		3 => '主管审核',
		4 => '经理审核',
		5 => '审核通过',
		6 => '小组长驳回',
		7 => '大组长驳回',
		8 => '主管驳回',
		9 => '经理驳回',
	];

	/**
	 * 审核状态：对应数据库
	 * @var Array
	 */
	public static $examine_status = [
		1 => '待组长审核',
		2 => '待主管审核',
		3 => '组长驳回',
		4 => '主管驳回',
		5 => '审核通过',
		6 => '已作废',
	];

	/**
	 * 采购状态
	 * @var Array
	 */
	public static $purchase_status = [
		1 => '已采购',
		2 => '待采购',
		3 => '部分采购',
		4 => '已失效',
	];

	const PRODUCT_STATUS = [
		1 => '正常',
		2 => '断货',
		3 => '停产',
	];

	const TRANSFER_STATUS = [
		1 => '未传输',
		2 => '已传输',
	];

	const EXAMINE_STATUS = [
		1 => '待小组长审核',
		2 => '待大组长审核',
		3 => '待主管审核',
		4 => '待经理审核',
		5 => '审核通过',
		6 => '小组长驳回',
		7 => '大组长驳回',
		8 => '主管驳回',
		9 => '经理驳回',
	];

	const SALE_DATA = [
		'seven' => '前7天',
		'fift' => '前15天',
		'month' => '前30天',
	];

	const SEARCH_TYPE = [
		1 => 'bs.goods_sn',
		2 => 'p.product_name',
		3 => 'pur.purchaser',
	];

	const ROLES_FOR_EXAMINE = [4, 8];

	/**
	 * 各角色审核状态对应关系
	 * 8-组长/4-主管
	 * @var Interval
	 */
	public static $examine_type = [
		8 => [
			1 => 2,
			2 => 3,
			3 => 6,
		],
		4 => [
			1 => 5,
			2 => 4,
			3 => 6,
		],
	];

	const STORY_INTERCEPT_RESSION = [
		1 => '仓库互斥，相互不出需求',
	];

	/**
	 * 合并ABC需求
	 * @return Array()
	 */
	public static function purchaseUnoin($andWhere = '', $limit = 15) {

		$order = ' ORDER BY bs.create_date DESC ';
		$sql = '
				SELECT bs.*, p.thumb_url, pur.delivery_info, ps.stocking_type_char, pur.purchase_price, pur.purchaser, p.product_status, p.product_name, pws.last_storage_time, pws.base_stock, pws.stock_can_allot, p.shelf_time, bs.platform_warehouse, pws.life_cycle,pws.sale_stock_pre, bs.examine_status
				FROM b_story bs JOIN p_product p ON bs.goods_sn = p.goods_sn
				LEFT JOIN p_product_purchase pur ON bs.goods_sn = pur.goods_sn
				LEFT JOIN (SELECT goods_sn, warehouse_code, stocking_type_char FROM p_special_purchase GROUP BY warehouse_code, goods_sn) ps ON bs.goods_sn = ps.goods_sn AND bs.warehouse_code = ps.warehouse_code
				LEFT JOIN p_product_warehouse_stock pws ON bs.goods_sn = pws.goods_sn AND bs.warehouse_code = pws.warehouse_code where 1
			' . $andWhere . $order . $limit;
		$result = self::mysql()->fetchAll($sql);
		$temp = self::purchaseSale($result);
		return $temp;
	}

	/**
	 * 取得特采分页数
	 * @return interger
	 */
	public static function fetchCountDemands($andWhere = '') {

		$sql = '
				SELECT count(*)
				FROM b_story bs JOIN p_product p ON bs.goods_sn = p.goods_sn
				LEFT JOIN p_product_purchase pur ON bs.goods_sn = pur.goods_sn
				LEFT JOIN (SELECT goods_sn, warehouse_code, stocking_type_char FROM p_special_purchase GROUP BY warehouse_code, goods_sn) ps ON bs.goods_sn = ps.goods_sn AND bs.warehouse_code = ps.warehouse_code
				LEFT JOIN p_product_warehouse_stock pws ON bs.goods_sn = pws.goods_sn AND bs.warehouse_code = pws.warehouse_code where 1
			' . $andWhere . " ORDER BY bs.id desc";
		return self::mysql()->fetchOne($sql);
	}

	/**
	 * 通过传输需求取得销量并存入原来的对应的数组当中
	 * @param 特采数据
	 * @return Array
	 */
	public static function purchaseSale($data) {
		$fields = [
			 'seven'=>'sale_7',
			 'fift'=>'sale_15',
			 'month'=>'sale_30',
		];
		foreach ($data as $k => $v) {
			$salesWhere=self::mysql()->quoteInto('goods_sn=?',$v['goods_sn']);
			$salesWhere.=" AND ".self::mysql()->quoteInto('warehouse_code=?',$v['warehouse_code']);
			$sales = self::mysql()->fetchRow('SELECT * FROM p_product_sales_statistics '.$where);
								
			foreach ($fields as $key => $fieldsName) {
				$data[$k]['sales_'][$key] = $sales[$fieldsName]??0;
				$data[$k]['sales'][$key] = $values['real_'.$fieldsName]??0;
			}					
		}
		return $data;
	}


	/**
	 * 合并WHERE条件
	 * @return $where string
	 */
	public static function purchaseWhere($data) {
		$where = ' AND ';
		//产品分类和仓库权限限制
		$userOwnCategory = Application_Model_User::getCategory();
		$where .= (empty($userOwnCategory)) ? " 1=0 AND " : (self::mysql()->quoteInto('p.catalog_id in  (?)', $userOwnCategory) . ' AND ');
		$userOwnWarehouse = Application_Model_User::getWarehouse();
		$where .= (empty($userOwnWarehouse)) ? " 1=0 AND " : (self::mysql()->quoteInto('bs.warehouse_code in (?)', $userOwnWarehouse) . ' AND ');

		if (isset($data['warehouse_code']) && !empty($data['warehouse_code'])) {
			$where .= ' bs.warehouse_code = "' . $data['warehouse_code'] . '" AND ';
		}
		if (isset($data['create_date'][0]) && !empty($data['create_date'][0]) && isset($data['create_date'][1]) && !empty($data['create_date'][1])) {
			$where .= ' (bs.create_date BETWEEN "' . $data['create_date'][0] . '" AND "' . $data['create_date'][1] . '") AND ';
		} else if (isset($data['create_date'][0]) && !empty($data['create_date'][0])) {
			$where .= ' bs.create_date >= "' . $data['create_date'][0] . '" AND ';
		} else if (isset($data['create_date'][1]) && !empty($data['create_date'][1])) {
			$where .= ' bs.create_date <= "' . $data['create_date'][1] . '" AND ';
		}
		if (isset($data['stock_from']) && ($data['stock_from'] != '') && isset($data['stock_to']) && ($data['stock_to'] != '')) {
			$where .= ' (pws.base_stock BETWEEN ' . (int) $data['stock_from'] . ' AND ' . (int) $data['stock_to'] . ') AND ';
		} elseif (isset($data['stock_from']) && ($data['stock_from'] != '')) {
			$where .= ' (pws.base_stock >= ' . (int) $data['stock_from'] . ') AND ';
		} elseif (isset($data['stock_to']) && ($data['stock_to'] != '')) {
			$where .= ' (pws.base_stock <= ' . (int) $data['stock_to'] . ') AND ';
		}
		if (isset($data['examine_status']) && !empty($data['examine_status'])) {
			$where .= ' bs.examine_status = ' . $data['examine_status'] . ' AND ';
		}
		if (isset($data['search_type']) && !empty($data['search_type']) && isset($data['search']) && !empty($data['search'])) {
			if ($data['search_type'] == 1) {
				$goods_sn_arr = explode(',', $data['search']);
				$where .= self::mysql()->quoteInto(self::SEARCH_TYPE[$data['search_type']] . ' in (?)', $goods_sn_arr) . ' AND ';
			} else {
				$where .= self::SEARCH_TYPE[$data['search_type']] . ' like "' . trim($data['search']) . '%" AND ';
			}
		}
		if (isset($data['transfer_status']) && !empty($data['transfer_status'])) {
			$where .= ' bs.transfer_status = ' . $data['transfer_status'] . ' AND ';
		}

		if (isset($data['catalog_id']) && $data['catalog_id']) {
			$productCatalogModel = new Application_Model_ProductCatalog();
			$catalogChildCatalog = $productCatalogModel->getAllChildCatalog((int) $data['catalog_id'], ['id']);
			if ($catalogChildCatalog) {
				$catalogChildCatalogId = array_column($catalogChildCatalog, 'id');
				$where .= self::mysql()->quoteInto("p.catalog_id in(?)", $catalogChildCatalogId) . ' AND ';
			}
		}
		//只显示未删除，待审核状态的需求
		$where .= ' bs.delete_flag=0 AND examine_status!=0';
		return rtrim($where, 'AND ');
	}

	/**
	 * 取得需求待确认列表
	 * @return Array
	 */
	public static function purchaseOfReject($where = '') {

		$sql = '
				SELECT bs.*, p.minimum_sn_amount, pws.last_storage_time, pws.base_stock, p.product_status, p.thumb_url, p.product_name, pur.purchase_price_base FROM b_reject_story bs
				JOIN p_product p ON bs.goods_sn = p.goods_sn
				LEFT JOIN p_product_purchase pur ON pur.goods_sn = bs.goods_sn AND pur.warehouse_code = bs.warehouse_code
				LEFT JOIN p_product_warehouse_stock pws ON p.goods_sn = pws.goods_sn AND bs.warehouse_code = pws.warehouse_code
				' . $where;
		$result = self::mysql()->fetchAll($sql);
		// 合并销量
		foreach ($result as $key => $values) {
			$result[$key]['sold'] = Base_Model_Sales::getOneSoldHistory($values['goods_sn'], $values['warehouse_code'], 0);
		}
		return $result;
	}
}

/*SELECT bs.id, bs.goods_sn, p.thumb_url, p.goods_name, p.sale_price_base, pws.stock_can_allot, spp.delivery_info, spp.examine_status,
spd.purchase_num, bs.a_story_quantity, bs.b_story_quantity, bs.c_story_quantity, spp.purchase_price_base, spd.stocking_type,
bs.sale_warning, p.product_status, bs.warehouse_code, p.shelf_time, pws.last_into_stock, spd.examine_status, spp.purchaser
FROM b_story bs JOIN p_product p ON bs.goods_sn = p.goods_sn
LEFT JOIN p_special_purchase spd ON bs.goods_sn = spd.goods_sn
JOIN p_product_warehouse_stock pws ON bs.goods_sn = pws.goods_sn
LEFT JOIN p_product_purchase spp ON bs.goods_sn = spp.goods_sn
WHERE pws.warehouse_code = p.wh_code AND bs.warehouse_code = p.wh_code*/